import pandas as pd
import numpy as np
from sklearn.metrics import cohen_kappa_score

# =========================
# 文件路径
# =========================
analysis_file = "Sentiments_Analysis_Output.xlsx"
original_file = "News Titles Sentiments_LLM and VADER.xlsx"

# =========================
# （1）Sampled_Grok_EQ_GPT：Human vs LLM
# =========================
sampled_df = pd.read_excel(
    analysis_file,
    sheet_name="Sampled_Grok_EQ_GPT"
)

# 确保无缺失
sampled_df = sampled_df.dropna(subset=["Human", "Sentiment"])

# Accuracy
sample_accuracy = (sampled_df["Human"] == sampled_df["Sentiment"]).mean()

# Cohen's kappa
sample_kappa = cohen_kappa_score(
    sampled_df["Human"],
    sampled_df["Sentiment"]
)

print("【Sampled Grok==GPT】")
print("Accuracy (LLM vs Human):", round(sample_accuracy, 4))
print("Cohen’s κ (LLM vs Human):", round(sample_kappa, 4))
print("-" * 50)

# =========================
# （2）构建 Final 列（逐 sheet 处理）
# =========================
xls = pd.ExcelFile(original_file)
problem_rows = []

final_sheets = {}

# 用于快速匹配抽样校对结果
sampled_key_cols = ["Year", "No."]
sampled_lookup = sampled_df.set_index(sampled_key_cols)["Human"].to_dict()

for year in xls.sheet_names:
    df = pd.read_excel(xls, sheet_name=year)
    df["Year"] = year
    df["Final"] = pd.Series(dtype="object")

    for idx, row in df.iterrows():
        key = (year, row["No."])

        # ① 抽样人工校对（最高优先级）
        if key in sampled_lookup:
            df.at[idx, "Human"] = sampled_lookup[key]
            df.at[idx, "Final"] = sampled_lookup[key]

        # ② 原始人工标注（LLM 分歧时）
        elif pd.notna(row["Human"]) and str(row["Human"]).strip() != "":
            df.at[idx, "Final"] = row["Human"]

        # ③ LLM 一致
        elif row["Grok"] == row["GPT"]:
            df.at[idx, "Final"] = row["Grok"]

        # ④ LLM 不一致且无人校对 → 记录问题
        else:
            problem_rows.append(
                df.loc[idx, ["Year", "No.", "Title", "Grok", "GPT"]]
            )

    final_sheets[year] = df

# =========================
# 输出存在问题的行（如果有）
# =========================
if problem_rows:
    problem_df = pd.DataFrame(problem_rows)
    print("⚠️ 发现 Grok ≠ GPT 且无人工校对的行数：", len(problem_df))
else:
    problem_df = pd.DataFrame()
    print("✅ 不存在 Grok ≠ GPT 且无人工校对的情况")

# =========================
# 保存 Final 结果
# =========================
final_output = "News_Titles_With_Final_Sentiment.xlsx"

with pd.ExcelWriter(final_output, engine="openpyxl") as writer:
    for year, df in final_sheets.items():
        df.to_excel(writer, sheet_name=year, index=False)
    if not problem_df.empty:
        problem_df.to_excel(writer, sheet_name="LLM_Disagree_No_Human", index=False)

# =========================
# （3）Final 列正确率估计
# =========================

# 数量统计
total_rows = sum(len(df) for df in final_sheets.values())

# 人工来源
human_rows = 0
llm_consensus_rows = 0

for df in final_sheets.values():
    human_rows += df["Human"].notna().sum()
    llm_consensus_rows += (
        (df["Human"].isna()) & (df["Grok"] == df["GPT"])
    ).sum()

# Final 正确率估计
final_accuracy_estimate = (
    human_rows * 1.0 +
    llm_consensus_rows * sample_accuracy
) / total_rows

print("-" * 50)
print("Final 列正确率估计：", round(final_accuracy_estimate, 4))
print("Final 文件已保存为：", final_output)
